/****** Object:  StoredProcedure [dbo].[AuthZ_spGetChildRoles]    Script Date: 06/24/2009 23:41:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetChildRoles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetChildRoles]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetChildRoles]    Script Date: 06/24/2009 23:41:40 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <4-13-2009>
-- Description:	<Retrieves all Groups and their related Roles.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetChildRoles]

	@role as nvarchar(50)

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		CREATE TABLE [dbo].[#TopRoles] (RoleID int, ParentRoleID int)

		INSERT INTO [dbo].[#TopRoles]

		/*Get the immediate children of the passed in role. */
		SELECT child.RoleID
		, child.ParentRoleID
		FROM [dbo].[Role] parent
		JOIN [dbo].[Role] child
		ON   child.ParentRoleID = parent.RoleID
		AND  parent.RoleName = @role

		WHILE @@ROWCOUNT > 0
		BEGIN

			INSERT INTO [dbo].[#TopRoles]

			/*Get the children's children. */
			SELECT child.RoleID
			, child.ParentRoleID
			FROM [dbo].[#TopRoles] parent
			JOIN [dbo].[Role] child
			ON   child.ParentRoleID = parent.RoleID  --Gets the children
			AND  not exists (           --Don't return rows that already exist.
			SELECT parent2.RoleID 
			FROM [dbo].[#TopRoles] parent2
			WHERE parent2.RoleID = child.RoleID)
		END

		SELECT 
		r.RoleName
		FROM [dbo].[#TopRoles] tr
		JOIN [dbo].[Role] r
		ON   r.RoleID = tr.RoleID

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


